use crate::entities::clients::ClientInput;
use crate::entities::color_parts::ColorPartInput;
use crate::entities::goods::GoodsInput;
use crate::entities::sale_items::SaleItemInput;
use crate::entities::sale_orders::SaleOrderInput;
use crate::entities::tickets::TicketInput;
use crate::entities::upload_files::FileInput;
use crate::entities::user_role::UserRoleInput;
use crate::entities::users::UserInput;
use crate::entities::vendors::VendorInput;
use crate::entities::{clients, color_parts, goods, sale_items, upload_files, users};
use futures::TryStreamExt;
use mongodb::bson::oid::ObjectId;
use mongodb::bson::{Bson, doc};
use mongodb::{Client, Collection};
use sea_orm::{
    ActiveModelTrait, ColumnTrait, ConnectionTrait, Database, DatabaseBackend, DatabaseConnection,
    EntityTrait, QueryFilter, QuerySelect, Statement, TransactionTrait,
};
use std::process::Command;

mod entities;

#[tokio::main]
async fn main() -> Result<(), Box<dyn std::error::Error>> {
    println!(
        "欢迎使用数据库迁移工具，开始之前请确保 MongoDB 和 PostgreSQL 已经运行，并且 PostgreSQL 已建立表但无数据"
    );
    // 等待用户按键
    let _ = Command::new("cmd").args(&["/C", "pause"]).status();

    let start = std::time::Instant::now();

    println!("开始连接数据库...");
    let client =
        Client::with_uri_str("mongodb://admin:admin@localhost:27017/zhongcai?authSource=zhongcai")
            .await?;
    let db = Database::connect("postgres://postgres:root@localhost:5432/zhongcaierp")
        .await
        .unwrap();
    println!("成功连接数据库...");

    migration_clients(&client, &db)
        .await
        .expect("迁移客户表数据过程出错！");
    migration_vendors(&client, &db)
        .await
        .expect("迁移供应商表数据过程出错！");
    migration_goods(&client, &db)
        .await
        .expect("迁移货物表数据过程出错！");
    migration_users(&client, &db)
        .await
        .expect("迁移用户表数据过程出错！");

    let client_tuples = clients::Entity::find()
        .select_only()
        .column(clients::Column::Id)
        .column(clients::Column::Uid)
        .into_tuple::<(i32, String)>()
        .all(&db)
        .await?;
    let user_tuples = users::Entity::find()
        .select_only()
        .column(users::Column::Id)
        .column(users::Column::Uid)
        .into_tuple::<(i32, String)>()
        .all(&db)
        .await?;

    migration_sale_orders(&client, &db, &client_tuples, &user_tuples)
        .await
        .expect("迁移销售数据过程出错！");
    migration_tickets(&client, &db, &client_tuples, &user_tuples)
        .await
        .expect("迁移工单表数据过程出错！");

    println!("删除客户表临时列：uid");
    let sql = r#"ALTER TABLE clients DROP COLUMN uid"#;
    db.execute(Statement::from_string(
        DatabaseBackend::Postgres,
        sql.to_string(),
    ))
    .await?;
    println!("删除货物表临时列：uid");
    let sql = r#"ALTER TABLE goods DROP COLUMN uid"#;
    db.execute(Statement::from_string(
        DatabaseBackend::Postgres,
        sql.to_string(),
    ))
    .await?;
    println!("删除用户表临时列：uid");
    let sql = r#"ALTER TABLE users DROP COLUMN uid"#;
    db.execute(Statement::from_string(
        DatabaseBackend::Postgres,
        sql.to_string(),
    ))
    .await?;

    println!("所有数据已迁移完成！");

    println!("耗时：{}秒", start.elapsed().as_secs());

    // 等待用户按键
    let _ = Command::new("cmd").args(&["/C", "pause"]).status();
    Ok(())
}

async fn migration_clients(
    client: &Client,
    db: &DatabaseConnection,
) -> Result<(), Box<dyn std::error::Error>> {
    let my_coll: Collection<ClientInput> = client.database("zhongcai").collection("clients");
    let mut cursor = my_coll.find(doc! {}).await?;

    println!("为客户表新建临时列：uid");
    let sql = r#"ALTER TABLE clients ADD COLUMN uid VARCHAR"#;
    db.execute(Statement::from_string(
        DatabaseBackend::Postgres,
        sql.to_string(),
    ))
    .await?;

    println!("开始迁移客户表数据...");
    let mut index = 0;
    while let Some(doc) = cursor.try_next().await? {
        index += 1;
        println!("开始迁移第{}条数据：{}", index, doc.clone().name);
        doc.into_active_model().insert(db).await?;
        println!("第{}条数据迁移成功！", index);
    }
    println!("客户表共{}条数据迁移成功！", index);
    println!("======================================================================");

    Ok(())
}

async fn migration_vendors(
    client: &Client,
    db: &DatabaseConnection,
) -> Result<(), Box<dyn std::error::Error>> {
    let my_coll: Collection<VendorInput> = client.database("zhongcai").collection("vendors");
    let mut cursor = my_coll.find(doc! {}).await?;

    println!("开始迁移供应商表数据...");
    let mut index = 0;
    while let Some(doc) = cursor.try_next().await? {
        index += 1;
        println!("开始迁移第{}条数据：{}", index, doc.clone().name);
        doc.into_active_model().insert(db).await?;
        println!("第{}条数据迁移成功！", index);
    }
    println!("供应商共{}条数据迁移成功！", index);
    println!("======================================================================");

    Ok(())
}

async fn migration_goods(
    client: &Client,
    db: &DatabaseConnection,
) -> Result<(), Box<dyn std::error::Error>> {
    let my_coll: Collection<GoodsInput> = client.database("zhongcai").collection("goods");
    let mut cursor = my_coll.find(doc! {}).await?;

    println!("为货物表新建临时列：uid");
    let sql = r#"ALTER TABLE goods ADD COLUMN uid VARCHAR"#;
    db.execute(Statement::from_string(
        DatabaseBackend::Postgres,
        sql.to_string(),
    ))
    .await?;

    println!("开始迁移货物表数据...");
    let mut index = 0;
    while let Some(doc) = cursor.try_next().await? {
        index += 1;
        println!("开始迁移第{}条数据：{}", index, doc.clone().name);
        doc.into_active_model().insert(db).await?;
        println!("第{}条数据迁移成功！", index);
    }
    println!("货物表共{}条数据迁移成功！", index);
    println!("======================================================================");

    Ok(())
}

async fn migration_users(
    client: &Client,
    db: &DatabaseConnection,
) -> Result<(), Box<dyn std::error::Error>> {
    let my_coll: Collection<UserInput> = client.database("zhongcai").collection("users");
    let mut cursor = my_coll.find(doc! {}).await?;

    println!("为用户表新建临时列：uid");
    let sql = r#"ALTER TABLE users ADD COLUMN uid VARCHAR"#;
    db.execute(Statement::from_string(
        DatabaseBackend::Postgres,
        sql.to_string(),
    ))
    .await?;
    let roles_tuple = [
        ("6610add72c023f4028b00a57".to_string(), 1),
        ("6610add72c023f4028b00a58".to_string(), 2),
        ("6610add72c023f4028b00a59".to_string(), 3),
        ("6610add72c023f4028b00a5a".to_string(), 4),
        ("6610add72c023f4028b00a5b".to_string(), 5),
        ("6610add72c023f4028b00a5c".to_string(), 6),
        ("6610add72c023f4028b00a5d".to_string(), 7),
    ];

    println!("开始迁移用户表数据...");
    let mut index = 0;
    while let Some(mut doc) = cursor.try_next().await? {
        index += 1;
        let role_ids: Vec<ObjectId> = doc
            .roles
            .iter()
            .filter_map(|bson| {
                match bson {
                    Bson::DbPointer(_) => None,
                    Bson::Document(doc) => {
                        // 处理 { "$ref": "...", "$id": ObjectId(...) } 格式
                        doc.get("$id").and_then(|id_bson| id_bson.as_object_id())
                    }
                    _ => None,
                }
            })
            .collect();

        let txn = db.begin().await?;

        let avatar_id = ObjectId::parse_str(doc.clone().avatar.unwrap_or("".to_string()))
            .unwrap_or(ObjectId::new());
        let file_coll: Collection<FileInput> =
            client.database("zhongcai").collection("uploadFiles");
        let mut file_cursor = file_coll.find(doc! {"_id": avatar_id}).await?;
        while let Some(mut file_doc) = file_cursor.try_next().await? {
            file_doc.name = file_doc.id.to_hex() + ".jpg";
            doc.avatar = Some(file_doc.name.clone());
            file_doc.url = Some(file_doc.name.clone());
            file_doc.into_active_model().insert(&txn).await?;
        }

        println!("开始迁移第{}条数据：{}", index, doc.clone().name);
        let user = doc.into_active_model().insert(&txn).await?;
        for role_id in role_ids {
            let role_id = role_id.to_hex();
            let role_id = roles_tuple.iter().find(|(id, _)| id == &role_id).unwrap();
            let user_role = UserRoleInput {
                user_id: user.id.clone(),
                role_id: role_id.1,
            };
            user_role.into_active_model().insert(&txn).await?;
        }
        txn.commit().await?;
        println!("第{}条数据迁移成功！", index);
    }
    println!("用户表共{}条数据迁移成功！", index);
    println!("======================================================================");

    Ok(())
}

async fn migration_sale_orders(
    client: &Client,
    db: &DatabaseConnection,
    c_tuples: &Vec<(i32, String)>,
    u_tuples: &Vec<(i32, String)>,
) -> Result<(), Box<dyn std::error::Error>> {
    let my_coll: Collection<SaleOrderInput> =
        client.database("zhongcai").collection("sale_invoices");
    let mut cursor = my_coll.find(doc! {}).await?;

    println!("开始迁移销售单数据...");
    let mut index = 0;
    while let Some(mut doc) = cursor.try_next().await? {
        index += 1;

        println!(
            "开始迁移第{}条数据，单号：{}",
            index,
            doc.clone().invoice_id
        );
        let txn = db.begin().await?;

        let sale_id = doc.id;
        let client_id = &doc.client;
        let biller_id = &doc.billing_p;

        let client_id = match client_id {
            Bson::DbPointer(_) => "".to_string(),
            Bson::Document(doc) => {
                // 处理 { "$ref": "...", "$id": ObjectId(...) } 格式
                doc.get("$id")
                    .and_then(|id_bson| id_bson.as_object_id())
                    .unwrap_or(ObjectId::new())
                    .to_hex()
            }
            _ => "".to_string(),
        };
        doc.client_id = c_tuples
            .iter()
            .find(|(_, uid)| uid.eq(&client_id))
            .unwrap_or(&(0, "".to_string()))
            .0;

        let biller_id = match biller_id {
            Bson::DbPointer(_) => "".to_string(),
            Bson::Document(doc) => {
                // 处理 { "$ref": "...", "$id": ObjectId(...) } 格式
                doc.get("$id")
                    .and_then(|id_bson| id_bson.as_object_id())
                    .unwrap_or(ObjectId::new())
                    .to_hex()
            }
            _ => "".to_string(),
        };
        doc.biller_id = u_tuples
            .iter()
            .find(|(_, uid)| uid.eq(&biller_id))
            .unwrap_or(&(0, "".to_string()))
            .0;

        let sale_order = doc.into_active_model().insert(&txn).await?;

        let mut items = vec![];
        let item_coll: Collection<SaleItemInput> =
            client.database("zhongcai").collection("sale_items");
        let mut item_cursor = item_coll.find(doc! {"saleInvoice.$id": sale_id}).await?;
        while let Some(mut item_doc) = item_cursor.try_next().await? {
            println!("正在插入项目：{:?}", item_doc.clone().name);
            item_doc.sale_id = sale_order.id;
            items.push(item_doc.into_active_model())
        }
        if !items.is_empty() {
            sale_items::Entity::insert_many(items).exec(&txn).await?;
        }

        txn.commit().await?;
        println!("第{}条数据迁移成功！", index);
    }
    println!("销售单共{}条数据迁移成功！", index);
    println!("======================================================================");

    Ok(())
}

async fn migration_tickets(
    client: &Client,
    db: &DatabaseConnection,
    c_tuples: &Vec<(i32, String)>,
    u_tuples: &Vec<(i32, String)>,
) -> Result<(), Box<dyn std::error::Error>> {
    let my_coll: Collection<TicketInput> = client.database("zhongcai").collection("tickets");
    let mut cursor = my_coll.find(doc! {}).await?;

    let goods_tuples = goods::Entity::find()
        .select_only()
        .column(goods::Column::Id)
        .column(goods::Column::Uid)
        .into_tuple::<(i32, String)>()
        .all(db)
        .await?;

    println!("开始迁移工单表数据...");
    let mut index = 0;
    while let Some(mut doc) = cursor.try_next().await? {
        index += 1;

        println!(
            "开始迁移第{}条数据，工单号：{}",
            index,
            doc.clone().plate_id
        );
        let txn = db.begin().await?;

        let sale_id = doc.id;
        let client_id = &doc.client;
        let plate_id = &doc.plate;
        let setup_user_id = &doc.setup_p;
        let file_maker_id = &doc.file_p;
        let check_user_id = &doc.check_p;

        let client_id = match client_id {
            Bson::DbPointer(_) => "".to_string(),
            Bson::Document(doc) => {
                // 处理 { "$ref": "...", "$id": ObjectId(...) } 格式
                doc.get("$id")
                    .and_then(|id_bson| id_bson.as_object_id())
                    .unwrap_or(ObjectId::new())
                    .to_hex()
            }
            _ => "".to_string(),
        };
        doc.client_id = c_tuples
            .iter()
            .find(|(_, uid)| uid.eq(&client_id))
            .unwrap_or(&(0, "".to_string()))
            .0;

        if let Some(plate_id) = plate_id {
            let plate_id = match plate_id {
                Bson::DbPointer(_) => "".to_string(),
                Bson::Document(doc) => doc
                    .get("$id")
                    .and_then(|id_bson| id_bson.as_object_id())
                    .unwrap_or(ObjectId::new())
                    .to_hex(),
                _ => "".to_string(),
            };
            doc.goods_id = Some(
                goods_tuples
                    .iter()
                    .find(|(_, uid)| uid.eq(&plate_id))
                    .unwrap_or(&(0, "".to_string()))
                    .0,
            );
        }

        let setup_user_id = match setup_user_id {
            Bson::DbPointer(_) => "".to_string(),
            Bson::Document(doc) => doc
                .get("$id")
                .and_then(|id_bson| id_bson.as_object_id())
                .unwrap_or(ObjectId::new())
                .to_hex(),
            _ => "".to_string(),
        };
        doc.setup_user_id = u_tuples
            .iter()
            .find(|(_, uid)| uid.eq(&setup_user_id))
            .unwrap_or(&(0, "".to_string()))
            .0;

        if let Some(file_maker_id) = file_maker_id {
            let file_maker_id = match file_maker_id {
                Bson::DbPointer(_) => "".to_string(),
                Bson::Document(doc) => doc
                    .get("$id")
                    .and_then(|id_bson| id_bson.as_object_id())
                    .unwrap_or(ObjectId::new())
                    .to_hex(),
                _ => "".to_string(),
            };
            doc.file_maker_id = Some(
                goods_tuples
                    .iter()
                    .find(|(_, uid)| uid.eq(&file_maker_id))
                    .unwrap_or(&(0, "".to_string()))
                    .0,
            );
        }

        if let Some(check_user_id) = check_user_id {
            let check_user_id = match check_user_id {
                Bson::DbPointer(_) => "".to_string(),
                Bson::Document(doc) => doc
                    .get("$id")
                    .and_then(|id_bson| id_bson.as_object_id())
                    .unwrap_or(ObjectId::new())
                    .to_hex(),
                _ => "".to_string(),
            };
            doc.check_user_id = Some(
                goods_tuples
                    .iter()
                    .find(|(_, uid)| uid.eq(&check_user_id))
                    .unwrap_or(&(0, "".to_string()))
                    .0,
            );
        }

        let ticket = doc.clone().into_active_model().insert(&txn).await?;

        let mut items = vec![];
        let item_coll: Collection<ColorPartInput> =
            client.database("zhongcai").collection("color_parts");
        let mut item_cursor = item_coll.find(doc! {"ticket.$id": sale_id}).await?;
        while let Some(mut item_doc) = item_cursor.try_next().await? {
            println!("正在插入项目：{:?}", item_doc.clone().name);
            item_doc.ticket_id = ticket.id;
            items.push(item_doc.into_active_model())
        }
        if !items.is_empty() {
            color_parts::Entity::insert_many(items).exec(&txn).await?;
        }

        if let Some(images) = doc.image {
            let image_ids: Vec<&str> = images.split(",").collect();
            let mut files = vec![];

            for image_id in image_ids {
                println!("正在插入图片：{:?}", image_id);

                let image = upload_files::Entity::find()
                    .filter(upload_files::Column::Name.starts_with(image_id))
                    .one(db)
                    .await?;

                if let Some(image) = image {
                    println!("图片已存在数据库中，id是：{}", image.id);
                    continue;
                }

                let image_id = ObjectId::parse_str(image_id).unwrap_or(ObjectId::new());
                let file_coll: Collection<FileInput> =
                    client.database("zhongcai").collection("uploadFiles");
                let mut file_cursor = file_coll.find(doc! {"_id": image_id}).await?;
                while let Some(mut file_doc) = file_cursor.try_next().await? {
                    file_doc.name = file_doc.id.to_hex() + ".jpg";
                    file_doc.url = Some(file_doc.name.clone());
                    file_doc.ticket_id = Some(ticket.id);
                    files.push(file_doc.into_active_model());
                }
            }
            if !files.is_empty() {
                upload_files::Entity::insert_many(files).exec(&txn).await?;
            }
        }

        txn.commit().await?;
        println!("第{}条数据迁移成功！", index);
    }
    println!("工单表共{}条数据迁移成功！", index);
    println!("======================================================================");

    Ok(())
}
